package hutech.ksv.dao;

import hutech.ksv.entity.ThongTinQuanAnEntity;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class DuyetQuanAnDao implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	@Autowired
	DataSource dataSource;
	
	public List<ThongTinQuanAnEntity> getTatCacThongTinQuanAn() {
		String query = "SELECT * FROM `taikhoancqa` AS tkcqa "
				+ "INNER JOIN `chuquanan` AS cqa ON (`cqa`.`MaCQA` = `tkcqa`.`MaCQA`) "
				+ "INNER JOIN `quanan` AS qa ON (`qa`.`TenTKCQA` = `tkcqa`.`TenTKCQA`) "
				+ "WHERE `qa`.`TrangThaiDuyetQA` = 0";
		List<ThongTinQuanAnEntity> listThongTinQuanAn = new ArrayList<ThongTinQuanAnEntity>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				ThongTinQuanAnEntity thongTinQuanAn = new ThongTinQuanAnEntity();
				thongTinQuanAn.setTenTKCQA(rs.getString("TenTKCQA"));
				thongTinQuanAn.setMaCQA(rs.getInt("MaCQA"));
				thongTinQuanAn.setTenCQA(rs.getString("TenCQA"));
				thongTinQuanAn.setDiaChiCQA(rs.getString("DiaChiCQA"));
				thongTinQuanAn.setGioiTinhCQA(rs.getString("GioiTinhCQA"));
				thongTinQuanAn.setSdtCQA(rs.getString("SDTCQA"));
				thongTinQuanAn.setEmailCQA(rs.getString("EmailCQA"));
				thongTinQuanAn.setMaQA(rs.getInt("MaQA"));
				thongTinQuanAn.setDiaChiQA(rs.getString("DiaChiQA"));
				thongTinQuanAn.setMoTaQA(rs.getString("MoTaQA"));				
				listThongTinQuanAn.add(thongTinQuanAn);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listThongTinQuanAn;
	}
	
	public int duyetQuanAn(int maQA) {
		String query = "UPDATE `quanan` SET `TrangThaiDuyetQA` = 1 WHERE `MaQA` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setInt(1, maQA);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	

}
